﻿Imports DTO
Imports System.Data.OleDb

Public Class MonHocDAO

    Public Function LayBang() As DataTable
        Dim dt As New DataTable
        Dim conn As OleDbConnection = DataProvider.ConnectDB()
        Dim strSQL As String = "SELECT * FROM MONHOC"
        Dim da As OleDbDataAdapter = New OleDbDataAdapter(strSQL, conn)
        da.Fill(dt)
        conn.Close()
        Return dt
    End Function

    Public Function LaySoLuongMonHoc() As Integer
        Dim conn As OleDbConnection = DataProvider.ConnectDB()
        Dim strSQL As String = "SELECT COUNT(*) FROM MONHOC"
        Dim cm As New OleDbCommand(strSQL, conn)
        Return cm.ExecuteScalar()
    End Function

    Public Function LayDanhSach() As List(Of MonHocDTO)
        Dim ds As New List(Of MonHocDTO)
        Dim conn As OleDbConnection = DataProvider.ConnectDB()
        Dim strSQL As String = "SELECT * FROM MONHOC"
        Dim cmd As New OleDbCommand(strSQL, conn)
        Dim reader As OleDbDataReader = cmd.ExecuteReader()
        While (reader.Read())
            Dim mhDTO As New MonHocDTO
            mhDTO.MaMon = reader("MaMon")
            mhDTO.TenMon = reader("TenMon")
            ds.Add(mhDTO)
        End While
        conn.Close()
        Return ds
    End Function

    Public Function ThemMonHoc(ByVal mhDTO As MonHocDTO) As Integer
        Dim conn As OleDbConnection = DataProvider.ConnectDB()
        Dim strSQL As String = "INSERT INTO MONHOC(TenMon) VALUES('" + mhDTO.TenMon + "')"
        Dim cmd As New OleDbCommand(strSQL, conn)
        Dim r As Integer = cmd.ExecuteNonQuery()
        conn.Close()
        Return r
    End Function


    Public Function XoaMonHoc(ByVal mhDTO As MonHocDTO) As Integer
        Dim conn As OleDbConnection = DataProvider.ConnectDB()
        Dim strSQL As String = "DELETE FROM MONHOC WHERE MaMon=" + mhDTO.MaMon.ToString
        Dim cmd As New OleDbCommand(strSQL, conn)
        Dim r As Integer = cmd.ExecuteNonQuery()
        conn.Close()
        Return r
    End Function


    Public Function CapNhatMonHoc(ByVal mhDTO As MonHocDTO) As Integer
        Dim conn As OleDbConnection = DataProvider.ConnectDB()
        Dim strSQL As String = "UPDATE MONHOC SET TenMon='" + mhDTO.TenMon + "' WHERE MaMon=" + mhDTO.MaMon.ToString()
        Dim cmd As New OleDbCommand(strSQL, conn)
        Dim r As Integer = cmd.ExecuteNonQuery()
        conn.Close()
        Return r
    End Function

End Class
